#!/usr/bin/env node

const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs').promises;

// SQLite database path
const sqliteDbPath = path.join(__dirname, '../resources/db/db.database');
const dicPath = path.join(__dirname, '../resources/dic');

async function getDicWords() {
  console.log('Scanning dic directory for word files...');
  
  const dicWords = new Set();
  
  // Read all letter directories
  const letterDirs = await fs.readdir(dicPath);
  
  for (const letterDir of letterDirs) {
    const letterPath = path.join(dicPath, letterDir);
    const stat = await fs.stat(letterPath);
    
    // Skip if not a directory
    if (!stat.isDirectory()) {
      continue;
    }
    
    try {
      const files = await fs.readdir(letterPath);
      for (const file of files) {
        // Get filename without extension as the word
        const word = path.parse(file).name.toLowerCase();
        dicWords.add(word);
      }
    } catch (err) {
      console.warn(`Could not read directory ${letterPath}: ${err.message}`);
    }
  }
  
  console.log(`Found ${dicWords.size} words in dic files`);
  return dicWords;
}

async function cleanupEnwordsTables() {
  console.log('Starting cleanup of enwords tables...');
  
  // Get all words from dic files
  const dicWords = await getDicWords();
  
  // Connect to SQLite database
  const sqliteDb = new sqlite3.Database(sqliteDbPath);
  
  // Wrap database operations in a promise
  const dbAll = (sql, params = []) => {
    return new Promise((resolve, reject) => {
      sqliteDb.all(sql, params, (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  };
  
  const dbRun = (sql, params = []) => {
    return new Promise((resolve, reject) => {
      sqliteDb.run(sql, params, (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  };
  
  try {
    // Find all enwords_* tables
    const tables = await dbAll(
      "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'enwords_%'"
    );
    
    console.log(`Found ${tables.length} enwords tables to process`);
    
    let totalDeleted = 0;
    
    // Process each table
    for (const table of tables) {
      const tableName = table.name;
      console.log(`Processing table: ${tableName}`);
      
      // Get all words from this table
      const words = await dbAll(`SELECT id, word FROM ${tableName}`);
      console.log(`Found ${words.length} words in ${tableName}`);
      
      // Check each word and delete if not in dic files
      let deletedCount = 0;
      for (const row of words) {
        const word = row.word.toLowerCase();
        if (!dicWords.has(word)) {
          await dbRun(`DELETE FROM ${tableName} WHERE id = ?`, [row.id]);
          deletedCount++;
          totalDeleted++;
        }
      }
      
      console.log(`Deleted ${deletedCount} words from ${tableName}`);
    }
    
    console.log(`Total deleted: ${totalDeleted} words across all tables`);
    console.log('Cleanup completed successfully');
    
  } catch (err) {
    console.error('Error during cleanup:', err);
  } finally {
    sqliteDb.close();
  }
}

// Run the cleanup function
cleanupEnwordsTables();